type BaseType = string | number | boolean | null | undefined

type ComparisonOperator = '>' | '<' | '>=' | '<=' | '!=' | '$like'

// 添加一个辅助类型，确保对象只包含一个属性
type ExactlyOne<T, K extends keyof T = keyof T>
  = K extends keyof T
    ? { [P in K]: T[P] } & { [P in Exclude<keyof T, K>]?: never }
    : never

type StrictComparisonType = ExactlyOne<Record<ComparisonOperator, BaseType>>

type TValue = BaseType | StrictComparisonType

export interface TQuery {
  [key: string]: TValue
}
export interface DataBaseOptions {
  where?: TQuery | TQuery[]
  order?: 'DESC' | 'ASC'
  limit?: number
}
export interface DataBase {
  create: <T>(tableName: string, value: Record<string, any> | Record<string, any>[]) => Promise<T | void | null>
  findBy: <T>(tableName: string, options: DataBaseOptions) => Promise<T[] | void | null>
  update: (tableName: string, value: Record<string, any>, options: Pick<DataBaseOptions, 'where'>) => Promise<any>
  destroy: (tableName: string, options: Pick<DataBaseOptions, 'where'>) => Promise<any>
  count: (tableName: string, options: Pick<DataBaseOptions, 'where'>) => Promise<any>
}

export interface DataBaseConfig {
  database?: 'mysql' | 'mongodb'
  id?: 'id' | '_id'
}

export function useDb(db: DataBase, config: DataBaseConfig = { id: '_id' }) {
  return {
    /**
     * 添加一条记录到表
     * @param {string} tableName 表名
     * @param {Record<string, any>} data 数据
     */
    addOne(tableName: string, data: Record<string, any>) {
      return db.create(tableName, data)
    },
    /**
     * 添加多条记录到表
     * @param {string} tableName 表名
     * @param {Record<string, any>[]} data 数据
     */
    addList(tableName: string, data: Record<string, any>[]) {
      return db.create(tableName, data)
    },
    /**
     * 删除一条记录
     * @param {string} tableName 表名
     * @param {string | number} id 记录的id
     */
    removeById(tableName: string, id: string | number) {
      return db.destroy(tableName, { where: { [config.id || '_id']: id } })
    },
    /**
     * 根据条件删除记录
     * @param {string} tableName 表名
     * @param {TQuery | TQuery[]} query 查询条件
     */
    removeBy(tableName: string, where: TQuery | TQuery[]) {
      return db.destroy(tableName, { where })
    },
    /**
     * 根据id更新记录
     * @param {string} tableName 表名
     * @param {string | number} id 记录的id
     * @param {Record<string, any>} data 更新的数据
     */
    updateById(tableName: string, id: string | number, data: Record<string, any>) {
      return db.update(tableName, data, { where: { [config.id || '_id']: id } })
    },
    /**
     * 根据条件更新记录
     * @param {string} tableName 表名
     * @param {Record<string, any>} data 更新的数据
     * @param {TQuery | TQuery[]} query 查询条件
     */
    updateBy(tableName: string, data: Record<string, any>, where: TQuery | TQuery[]) {
      return db.update(tableName, data, { where })
    },
    /**
     * 根据id查询记录
     * @param {string} tableName 表名
     * @param {string | number} id id
     * @returns {Promise<Record<string, any>>} 查询结果
     */
    findById(tableName: string, id: string | number) {
      return db.findBy(tableName, { where: { [config.id || '_id']: id } })
    },
    /**
     * 根据条件查询记录
     * @param {string} tableName 表名
     * @param {DataBaseOptions} options 查询条件
     * @returns {Promise<Record<string, any>[]>} 查询结果
     */
    findBy(tableName: string, options = {} as DataBaseOptions) {
      return db.findBy(tableName, options)
    },
  }
}

/**
 * demo
 * const mysql = require('mysql2/promise')
class Database {
  constructor(config) {
    this.pool = mysql.createPool(config)
  }

  async query(sql, values) {
    const connection = await this.pool.getConnection()
    try {
      const [rows] = await connection.query(sql, values)
      return rows
    }
    finally {
      connection.release()
    }
  }

  close() {
    return this.pool.end()
  }
}
 */

// const db = {
//   async query(sql: string, values: any[] = []): Promise<any> {
//     // 模拟数据库查询逻辑，实际应根据具体数据库实现替换
//     console.log('Executing SQL:', sql, 'with values:', values)
//     return { sql, values } // 示例返回空数组，实际应返回查询结果
//   },
// }
// 辅助函数，将查询条件对象转换为SQL WHERE语句
// function mapSqlQueryConversion(query: TQuery | TQuery[]): { sql: string, values: any[] } {
//   const values: any[] = []
//   const sql: string[] = []
//   function parseQueryObj(where: TQuery) {
//     const _sql = Object.keys(where).map((key) => {
//       const conversion = where[key] as StrictComparisonType
//       if (typeof conversion === 'object') {
//         const operation = Object.keys(conversion)[0] as ComparisonOperator
//         const value = conversion[operation]
//         values.push(value)
//         return `${key} ${operation} ?`
//       }
//       values.push(conversion)
//       return `${key} = ?`
//     }).join(' AND ')
//     sql.push(_sql)
//   }
//   if (Array.isArray(query)) {
//     query.forEach(item => parseQueryObj(item))
//   }
//   else {
//     parseQueryObj(query)
//   }
//   return { sql: sql.join(' OR '), values }
// }
// const myDb = useDb({
//   create(tableName, data) {
//     let keys = []
//     let values = []
//     if (Array.isArray(data)) {
//       keys = Object.keys(data[0])
//       values = data.map(item => Object.values(item))
//     }
//     else {
//       keys = Object.keys(data)
//       values = Object.values(data)
//     }
//     const placeholders = keys.map(() => '?').join(',')

//     return db.query(`INSERT INTO ${tableName} (${keys.join(',')}) VALUES (${placeholders})`, values)
//   },
//   findBy(tableName, options = {} as DataBaseOptions) {
//     let sql = `SELECT * FROM ${tableName}`
//     let values = []

//     if (options.where) {
//       const res = mapSqlQueryConversion(options.where)
//       sql += ` WHERE ${res.sql}`
//       values = res.values
//     }

//     if (options.order) {
//       sql += ` ORDER BY ${options.order}`
//     }

//     if (options.limit) {
//       sql += ` LIMIT ${options.limit}`
//     }

//     return db.query(sql, values)
//   },

//   update(tableName, data, options = {}) {
//     const setClause = Object.keys(data)
//       .map(key => `${key} = ?`)
//       .join(',')

//     const values = Object.values(data)
//     let sql = `UPDATE ${tableName} SET ${setClause}`

//     if (options.where) {
//       const res = mapSqlQueryConversion(options.where)
//       sql += ` WHERE ${res.sql}`

//       values.push(...res.values)
//     }

//     return db.query(sql, values)
//   },

//   destroy(tableName, options = {}) {
//     let sql = `DELETE FROM ${tableName}`
//     let values = []

//     if (options.where) {
//       const res = mapSqlQueryConversion(options.where)
//       sql += ` WHERE ${res.sql}`
//       values = res.values
//     }

//     return db.query(sql, values)
//   },

//   async count(tableName, options = {}) {
//     let sql = `SELECT COUNT(*) as count FROM ${tableName}`
//     let values = []

//     if (options.where) {
//       const res = mapSqlQueryConversion(options.where)
//       sql += ` WHERE ${res.sql}`
//       values = res.values
//     }

//     const res = await db.query(sql, values)
//     return res?.[0]?.count
//   },
// })

// myDb.addOne('users', { name: 'yi', age: 18 })

// myDb.addList('users', [
//   { name: 'yi', age: 18 },
//   { name: 'yi', age: 18 },
// ])

// myDb.removeById('users', 1)

// myDb.removeBy('users', { name: 'yi', age: 30 })

// myDb.updateById('users', 1, { name: 'yi', age: 18 })

// myDb.updateBy('users', { name: 'yi', age: 30, sex: 'dsfsdfdf' }, [{ sex: { '<': 28 }, name: { '>': 'dsfsdfdf' }, age: { '>=': 5 } }, { id: 25 }])

// myDb.findById('users', 1)

// myDb.findBy('users', { where: { name: 'yi', age: 30, sex: 'dsfsdfdf' } })
